<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2016/10/11
 * Time: 11:21
 * 针对所有站点订单数据的查询合并等存储过程 目前只针对所有在这台美国服务器上的数据
 */

function getBlackNameList($type)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[DB_ID]['server'];
        $port = $DATABASE_LIST[DB_ID]['port'];
        $username = $DATABASE_LIST[DB_ID]['username'];
        $password = $DATABASE_LIST[DB_ID]['password'];
        $database_name = $DATABASE_LIST[DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);
        $pdo->exec('SET NAMES utf8');
        if ($type == 'user_name') {
            $all = $pdo->query("select user_name from black_list")->fetchAll();
        } elseif ($type == 'phone') {
            $all = $pdo->query("select phone from black_list")->fetchAll();
        } elseif ($type == 'line') {
            $all = $pdo->query("select line from black_list")->fetchAll();
        } else {
            $all = $pdo->query("select email from black_list")->fetchAll();
        }

        return $all;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//根据条件来查所有站点该用户的订单
function OrderGetByCondition($search)
{

    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[DB_ID]['server'];
        $port = $DATABASE_LIST[DB_ID]['port'];
        $username = $DATABASE_LIST[DB_ID]['username'];
        $password = $DATABASE_LIST[DB_ID]['password'];
        $database_name = $DATABASE_LIST[DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');
        $dbs = $pdo->query("select * from dbs_config")->fetchAll();

        foreach ($dbs as $v) {
            $dbcontion_server = new PDO('mysql:dbname=' . $v["dbname"] . ';host=' . $v["host"] . ';port=' . $v["port"], $v["user"], $v["pwd"]);
            $dbcontion_server->query("set names utf8");
            $sql = "select * from jh_order_sn_record " . $search;
            $ret = $dbcontion_server->query($sql)->fetchAll();
            $allresults[$v['website']] = $ret;
        }
        return $allresults;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//根据每个网站来查购物车商品
function getShopCartBySn($sn, $web)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[DB_ID]['server'];
        $port = $DATABASE_LIST[DB_ID]['port'];
        $username = $DATABASE_LIST[DB_ID]['username'];
        $password = $DATABASE_LIST[DB_ID]['password'];
        $database_name = $DATABASE_LIST[DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');


        $sql = "select me.product_id as product_id,p.products_name as products_name,p.products_model as products_model,me.color as products_color,me.size as products_size,me.product_nums as num, (me.product_nums * p.products_special_price) as total_price from jh_shop_cart me, products p where me.product_id  = p.products_id and me.order_sn = '$sn'";

        $ret = $pdo->query($sql)->fetchAll();

        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

//根据每个网站的每個訂單來查詢快遞發貨情況
function getKdInfoBySn($sn, $web)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[DB_ID]['server'];
        $port = $DATABASE_LIST[DB_ID]['port'];
        $username = $DATABASE_LIST[DB_ID]['username'];
        $password = $DATABASE_LIST[DB_ID]['password'];
        $database_name = $DATABASE_LIST[DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');


        $sql = "select * from jh_logistics where order_sn = '$sn'";
        $ret = $pdo->query($sql)->fetch();
        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

//根据网站来查相应的产品
function getProductById($product_id, $web)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[DB_ID]['server'];
        $port = $DATABASE_LIST[DB_ID]['port'];
        $username = $DATABASE_LIST[DB_ID]['username'];
        $password = $DATABASE_LIST[DB_ID]['password'];
        $database_name = $DATABASE_LIST[DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');

        $db = $pdo->query("select * from dbs_config where website = '$web'")->fetch();


        $dbcontion_server = new PDO('mysql:dbname=' . $db["dbname"] . ';host=' . $db["host"] . ';port=' . $db["port"], $db["user"], $db["pwd"]);
        $dbcontion_server->query("set names utf8");
        $sql = "select * from products where products_id = '$product_id'";
        $ret = $dbcontion_server->query($sql)->fetch();
        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//用于后台处理
function createTemTable()
{

    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];


        $pdo = null;

        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "CREATE  TABLE IF NOT EXISTS `t_tmp_orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `web_site` VARCHAR(100) NOT NULL COMMENT '服务器站点',
  `order_sn` VARCHAR(50) NOT NULL COMMENT '订单号',
  `userid` VARCHAR(255) NOT NULL COMMENT '用户cookieID',
  `shipping_method` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '配送方式',
  `pay_type` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '付款方式',
  `user_name` VARCHAR(255) DEFAULT NULL COMMENT '收件人名稱',
  `shipping_time` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '收貨時間',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '聯絡電話',
  `line` VARCHAR(255) DEFAULT NULL COMMENT 'FB号码',
  `addres` VARCHAR(2000) DEFAULT NULL COMMENT '詳細地址',
  `gender` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '性別',
  `email` VARCHAR(255) DEFAULT NULL COMMENT '郵箱',
  `pay_info` text COMMENT '支付信息',
  `note` TEXT COMMENT '備註',
  `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '订单状态 0未完成  1完成',
  `createtime` INT(11) NOT NULL COMMENT '订单生成时间',
  `modifytime` INT(11) NOT NULL COMMENT '订单修改时间',
  PRIMARY KEY (`id`),
 UNIQUE KEY `web_order` (`web_site`,`order_sn`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单记录表';";


        $createDb = $pdo->exec($sql);
        return $createDb;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//查出所有库的所有订单数据
function getAllData()
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');
        $dbs = $pdo->query("select * from dbs_config")->fetchAll();


        foreach ($dbs as $v) {
            $dbcontion_server = new PDO('mysql:dbname=' . $v["dbname"] . ';host=' . $v["host"] . ';port=' . $v["port"], $v["user"], $v["pwd"]);
            $dbcontion_server->query("set names utf8");
            $sql = "select * from jh_order_sn_record ";
            $ret = $dbcontion_server->query($sql)->fetchAll();
            $allres[$v["website"]] = $ret;
        }

        return $allres;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


function dumpDataToAllTable($kd_data, $web)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');
        $db = $pdo->query("select * from dbs_config where website = '$web'")->fetch();

        $dbcontion_server = new PDO('mysql:dbname=' . $db["dbname"] . ';host=' . $db["host"] . ';port=' . $db["port"], $db["user"], $db["pwd"]);
        $dbcontion_server->query("set names utf8");

        $kd_name = $kd_data['kd_name'];
        $kd_name = "'$kd_name'";

        $sn = $kd_data['order_sn'];
        $sn = "'$sn'";

        $kd_url = $kd_data['kd_url'];
        $kd_url = "'$kd_url'";

        $kd_sn = $kd_data['kd_sn'];
        $kd_sn = "'$kd_sn'";

        $isGo = $kd_data['isGo'];
        $isGo = "'$isGo'";

        $ct = time();

        $mt = time();

        $sql = "replace into jh_logistics (`kd_name`,`order_sn`,`kd_url`,`kd_sn`,`isGo`,`createtime`,`modifytime`) values ($kd_name,$sn,$kd_url,$kd_sn,$isGo,$ct,$mt)";
        $ret = $dbcontion_server->exec($sql);
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//将查出的数据导入临时表中
function dumpDataToTmp($sql)
{

    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');

        $ret = $pdo->exec($sql);
        return $ret;
    } catch (PDOException $e) {
        echo $e->getMessage();
        exit;
    }
}

//后台订单管理
function getAllOrders($start, $page_size)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');

        $allret = $pdo->query("select * from `t_tmp_orders` where status = 0 limit $start,$page_size")->fetchAll();
        return $allret;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//后台订单数量查询接口
function getCountBySearch($search = '')
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');


        $allcount = $pdo->query("select count(*) as c from `jh_order_sn_record` $search")->fetch();
        return $allcount;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//后台根据条件查询订单
function getOrdersBySearch($start = '', $page_size = '', $search)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');

        if ($start) {
            $allresults = $pdo->query("select * from `jh_order_sn_record` $search limit $start,$page_size")->fetchAll();
        } else {
            $allresults = $pdo->query("select * from `jh_order_sn_record` $search ")->fetchAll();
        }

        return $allresults;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

function changeOrderSnStatus($sn)
{
    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');

        $sql = "UPDATE `jh_order_sn_record` SET `status` = 0 WHERE `order_sn` = '$sn'";
        $ret = $pdo->exec($sql);
        return $ret;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


function saveEmailData($data)
{

    try {
        global $DATABASE_LIST;
        $server = $DATABASE_LIST[OSA_DB_ID]['server'];
        $port = $DATABASE_LIST[OSA_DB_ID]['port'];
        $username = $DATABASE_LIST[OSA_DB_ID]['username'];
        $password = $DATABASE_LIST[OSA_DB_ID]['password'];
        $database_name = $DATABASE_LIST[OSA_DB_ID]['db_name'];

        $pdo = null;
        $pdo = new PDO('mysql:host=' . $server . ';port=' . $port . ';dbname=' . $database_name, $username, $password);

        $pdo->exec('SET NAMES utf8');

        $product_id = $data['products_info'];
        $product_id = "'$product_id'";

        $order_sn = $data['order_sn'];
        $order_sn = "'$order_sn'";

        $to_email = $data['to_email'];
        $to_email = "'$to_email'";

        if (isset($data['createtime'])) {
            $createtime = $data['createtime'];
            $modifytime = time();
            $sql = "replace into yiye_mailinfo(`products_info`,`order_sn`,`to_email`,`createtime`,`modifytime`) values ($product_id,$order_sn,$to_email,$createtime,$modifytime)";
        } else {
            $modifytime = time();
            $sql = "replace into yiye_mailinfo(`products_info`,`order_sn`,`to_email`,`modifytime`) values ($product_id,$order_sn,$to_email,$modifytime)";
        }


        $res = $pdo->exec($sql);
        return $res;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

function getheimaoStatusByAPI($kd_num)
{
    $no_html = http_get(trim($kd_num));//发送请求

    //因为不是数据接口，所以返回的是个html页面 所以要来抓取页面里面的东西
    $zz_res = format_html($no_html, trim($kd_num));
    return $zz_res[1];
}

define("URL", "http://www.t-cat.com.tw/Inquire/Trace.aspx?no=");
function http_get($value)
{
    $url = URL . $value;
    $ch = curl_init();
    $timeout = 60;
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
    $result = curl_exec($ch);
    curl_close($ch);
    return $result;
}


function format_html($html, $id)
{
    $result = '';
    if (empty($html)) {
        return $result;
    }


    $sub_str = "<table>";
    $sub_str_td = "您輸入的貨物追蹤查詢號碼如下";

    if (strpos($html, $sub_str)) {
        //位置开始后面的字符串
        $real_html = substr($html, 0, strpos($html, $sub_str));
        if (!empty($real_html) || strpos($real_html, '<tr>') != false) {
            $t1_html = substr($real_html, strpos($real_html, '<tr>'), (strlen($real_html) - strpos($real_html, '<tr>')));
            $t2_html = substr($t1_html, strpos($t1_html, '</tr>'), (strlen($t1_html) - strpos($t1_html, '</tr>')));
            //正则匹配
            $matchall = array();
            preg_match_all('/\<td.+?\>(.+?)\<\/td\>/', $t2_html, $matchall);

        }


        $string = strip_tags($matchall[0][0]);
        $string = htmlspecialchars($string, ENT_QUOTES, 'UTF-8');
        $nm[0] = trim($string);

        $string1 = strip_tags($matchall[0][1]);
        $string1 = htmlspecialchars($string1, ENT_QUOTES, 'UTF-8');
        $nm[1] = trim($string1);

        $string2 = strip_tags($matchall[0][2]);
        $string2 = htmlspecialchars($string2, ENT_QUOTES, 'UTF-8');
        $nm[2] = trim($string2);

        $string3 = strip_tags($matchall[0][3]);
        $string3 = htmlspecialchars($string3, ENT_QUOTES, 'UTF-8');
        $nm[3] = trim($string3);

        $result = $nm;

    } else if (strpos($html, $sub_str_td)) {
        //正在配送的状态
        $result = array($id, "未上线", "", "");
    } else {
        //非法的单号
        $result = array($id, "非法的单号", "", "");
    }

    return $result;
}